using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

using NUnit.Framework;

using NSimpleDB.Service.Contract;
using NSimpleDB.Service.VistaDb;


namespace spike.SqlStatements
{
    [TestFixture]
    public class spikes
    {
        [Test]
        public void spikeAndOrInPredicate()
        {
            System.IO.File.Delete("temp.vbd3");
            using (ISimpleDBService s = new NSimpleDB.Service.VistaDb.VistaDbSimpleDBService("temp.vbd3"))
            {
                s.CreateDomain("d");

                s.PutAttributes("d", "1", new SimpleDBAttribute("a", "a"),
                                          new SimpleDBAttribute("a", "c"));
                s.PutAttributes("d", "2", new SimpleDBAttribute("a", "a"),
                                          new SimpleDBAttribute("a", "b"));
                s.PutAttributes("d", "3", new SimpleDBAttribute("a", "c"));
                s.PutAttributes("d", "4", new SimpleDBAttribute("a", "a"),
                                          new SimpleDBAttribute("a", "c"),
                                          new SimpleDBAttribute("a", "d"));
                s.PutAttributes("d", "5", new SimpleDBAttribute("b", "a"),
                                          new SimpleDBAttribute("b", "b"),
                                          new SimpleDBAttribute("b", "c"));
                s.PutAttributes("d", "6", new SimpleDBAttribute("a", "d"));
            }

            string sql;
            DataTable tb;

            // ['a'='x']
            sql = "select distinct itemName from Domain0 where (attributeName='a' and attributeValue='x')";
            tb = DBHelpers.GetItemNamesBySql("temp.vbd3", sql);
            Assert.AreEqual(0, tb.Rows.Count);
            // ['a'='b']
            sql = "select distinct itemName from Domain0 where (attributeName='a' and attributeValue='b')";
            tb = DBHelpers.GetItemNamesBySql("temp.vbd3", sql);
            Assert.AreEqual(1, tb.Rows.Count);
            // ['a'='a']
            sql = "select distinct itemName from Domain0 where (attributeName='a' and attributeValue='a')";
            tb = DBHelpers.GetItemNamesBySql("temp.vbd3", sql);
            Assert.AreEqual(3, tb.Rows.Count);

            //---------------------------------------
            // ['a'='x' or 'a'='y']
            sql = @"select distinct itemName from Domain0 where 
                    (attributeName='a' and attributeValue='x') or 
                    (attributeName='a' and attributeValue='y')";
            tb = DBHelpers.GetItemNamesBySql("temp.vbd3", sql);
            Assert.AreEqual(0, tb.Rows.Count);

            // ['a'='b' or 'a'='x']
            sql = "select distinct itemName from Domain0 where (attributeName='a' and attributeValue='b') or (attributeName='a' and attributeValue='x')";
            tb = DBHelpers.GetItemNamesBySql("temp.vbd3", sql);
            Assert.AreEqual(1, tb.Rows.Count);

            // ['b'='a' or 'b'='b']
            sql = "select distinct itemName from Domain0 where (attributeName='b' and attributeValue='a') or (attributeName='b' and attributeValue='b')";
            tb = DBHelpers.GetItemNamesBySql("temp.vbd3", sql);
            Assert.AreEqual(1, tb.Rows.Count);

            // ['a'='a' or 'a'='d']
            sql = "select distinct itemName from Domain0 where (attributeName='a' and attributeValue='a') or (attributeName='a' and attributeValue='d')";
            tb = DBHelpers.GetItemNamesBySql("temp.vbd3", sql);
            Assert.AreEqual(4, tb.Rows.Count);

            //---------------------------------------
            // ['a'='b' and 'a'='c']
            sql = @"select distinct itemName from Domain0 where 
                    (attributeName='a' and attributeValue='b') and 
                    itemName in (select itemName from Domain0 where 
                                  attributeName='a' and attributeValue='c')";
            tb = DBHelpers.GetItemNamesBySql("temp.vbd3", sql);
            Assert.AreEqual(0, tb.Rows.Count);

            // ['a'='a' and 'a'='c']
            sql = @"select distinct itemName from Domain0 where 
                    (attributeName='a' and attributeValue='a') and 
                    itemName in (select itemName from Domain0 where 
                                  attributeName='a' and attributeValue='c')";
            tb = DBHelpers.GetItemNamesBySql("temp.vbd3", sql);
            Assert.AreEqual(2, tb.Rows.Count);

            // ['b'='a' and 'b'='c']
            sql = @"select distinct itemName from Domain0 where 
                    (attributeName='b' and attributeValue='a') and 
                    itemName in (select itemName from Domain0 where 
                                  attributeName='b' and attributeValue='c')";
            tb = DBHelpers.GetItemNamesBySql("temp.vbd3", sql);
            Assert.AreEqual(1, tb.Rows.Count);

            //---------------------------------------
            // ['a'='d' or 'a'='a' and 'a'='b' or 'a'='c']
            sql = @"select distinct itemName from Domain0 where 
                    (attributeName='a' and attributeValue='d') or
                    itemName in (
                        select itemName from Domain0 where
                            (attributeName='a' and attributeValue='a') and
                            itemName in (select itemName from Domain0 where
                                            (attributeName='a' and attributeValue='b'))
                                ) or
                    (attributeName='a' and attributeValue='c')";
            tb = DBHelpers.GetItemNamesBySql("temp.vbd3", sql);
            Assert.AreEqual(5, tb.Rows.Count);
        }
    }
}
